# -*- coding: utf-8 -*-
"""
openworship - Open Source Church Service Management
Copyright (c) 2008 Kevin Landers

Portions copyright (c) 2008 Raoul Snyman, Joshua Biagio, Michael Lucas and
Scott Guerrieri

This program is free software; you can redistribute it and/or modify it under
the terms of the GNU General Public License as published by the Free Software
Foundation; version 2 of the License.

This program is distributed in the hope that it will be useful, but WITHOUT ANY
WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A
PARTICULAR PURPOSE. See the GNU General Public License for more details.

You should have received a copy of the GNU General Public License along with
this program; if not, write to the Free Software Foundation, Inc., 59 Temple
Place, Suite 330, Boston, MA 02111-1307 USA
"""

"""
This module contains the table definitions.
"""

from datetime import datetime
from sqlalchemy import Column, Table, MetaData, types, ForeignKey

from openworship.model.meta import metadata

# Definition of the "users" table
members_table = Table('members', metadata,
    Column('id', types.Integer, primary_key=True),
    Column('email', types.Unicode(128), nullable=False, index=True),
    Column('password', types.Unicode(40), nullable=False),
    Column('first_name', types.Unicode(128), nullable=False),
    Column('last_name', types.Unicode(128), nullable=False),
    Column('created', types.DateTime, default=datetime.now()),
    Column('last_login', types.DateTime, default=datetime.now())
)

# Definition of the "roles" table
roles_table = Table('roles', metadata,
    Column('id', types.Integer, primary_key=True),
    Column('name',  types.Unicode(32), nullable=False, index=True),
    Column('description', types.Unicode(255))
)

# Definition of the "member roles" table
members_roles_table = Table('members_roles', metadata,
    Column('member_id', types.Integer, ForeignKey('members.id')),
    Column('role_id', types.Integer, ForeignKey('roles.id'))
)

# Definition of the "permissions" table
permissions_table = Table('permissions',  metadata,
    Column('id', types.Integer, primary_key=True),
    Column('name',  types.Unicode(32), nullable=False, index=True),
    Column('description', types.Unicode(255))
)

# Definition of the "permission roles" table
permissions_roles_table = Table('permissions_roles', metadata,
    Column('permission_id', types.Integer, ForeignKey('permissions.id')),
    Column('role_id', types.Integer, ForeignKey('roles.id'))
)

# Definition of the "church" table
churches_table = Table('churches', metadata,
    Column('id',  types.Integer,  primary_key=True),
    Column('name', types.Unicode(128), nullable=False),
    Column('telephone', types.Unicode(16), nullable=False),
    Column('facsimile',  types.Unicode(16)),
    Column('postal_address', types.UnicodeText),
    Column('physical_address', types.UnicodeText),
    Column('website', types.Unicode(128))
)

# Definition of the "songs" table
songs_table = Table('songs', metadata,
    Column('id', types.Integer, primary_key=True),
    Column('title', types.Unicode(128), nullable=False, index=True),
    Column('also_known_as', types.Unicode(128), index=True),
    Column('key_line', types.Unicode(255)),
    Column('copyright', types.Unicode(64)),
    Column('ccli_number', types.Unicode(16)),
    Column('catalog', types.Unicode(32)),
    Column('keys', types.Unicode(16)),
    Column('capo', types.Unicode(16)),
    Column('tempo', types.Unicode(32)),
    Column('time_sig', types.Unicode(16)),
    Column('hymnal', types.Unicode(128)),
    Column('hymn_number', types.Integer, index=True),
    Column('presentation_order', types.Unicode(32)),
    Column('lyrics', types.UnicodeText, nullable=False, index=True)
)

files_table = Table('files', metadata,
    Column('id', types.Integer, primary_key=True),
    Column('song_id', types.Integer, ForeignKey('songs.id'), nullable=False),
    Column('type', types.Unicode(128), nullable=False, index=True),
    Column('filename', types.Unicode(128), nullable=False),
    Column('filetype', types.Unicode(64), nullable=False),
    Column('filesize', types.Integer, nullable=False),
    Column('filedata', types.Binary, nullable=False)
)

# Definition of the "topics" table
topics_table = Table('topics', metadata,
    Column('id', types.Integer, primary_key=True),
    Column('name', types.Unicode(128), nullable=False, index=True)
)

# Definition of the "songs_topics" table
songs_topics_table = Table('songs_topics', metadata,
    Column('song_id', types.Integer, ForeignKey('songs.id'), primary_key=True),
    Column('topic_id', types.Integer, ForeignKey('topics.id'), primary_key=True)
)

# Definition of the "authors" table
authors_table = Table('authors', metadata,
    Column('id', types.Integer, primary_key=True),
    Column('name', types.Unicode(128), nullable=False)
)

# Definition of the "authors_songs" table
authors_songs_table = Table('authors_songs', metadata,
    Column('author_id', types.Integer, ForeignKey('authors.id'), nullable=False),
    Column('song_id', types.Integer, ForeignKey('songs.id'), nullable=False)
)

# Definition of the "bibles" table
bibles_table = Table('bibles', metadata,
    Column('id', types.Integer, primary_key=True),
    Column('version', types.Unicode(128), nullable=False),
    Column('copyright', types.Unicode(64))
)

# Definition of the "testaments" table
testaments_table = Table('testaments', metadata,
    Column('id', types.Integer, primary_key=True),
    Column('name', types.Unicode(32), nullable=False)
)

# Definition of the "books" table
books_table = Table('books',  metadata,
    Column('id', types.Integer, primary_key=True),
    Column('testament_id', types.Integer, ForeignKey('testaments.id'), nullable=False),
    Column('name', types.Unicode(64), nullable=False, index=True),
    Column('abbreviation', types.Unicode(4), index=True)
)

# Definition of the "bibles_books" joining table
bibles_books_table = Table('bibles_books', metadata,
    Column('bible_id', types.Integer, ForeignKey('bibles.id'), primary_key=True),
    Column('book_id', types.Integer, ForeignKey('books.id'), primary_key=True)
)

# Definition of the "verses" table
verses_table = Table('verses', metadata,
    Column('id', types.Integer, primary_key=True),
    Column('bible_id', types.Integer, ForeignKey('bibles.id'), nullable=False),
    Column('book_id', types.Integer, ForeignKey('books.id'), nullable=False),
    Column('chapter', types.Integer, nullable=False, index=True),
    Column('verse', types.Integer, nullable=False, index=True),
    Column('text', types.UnicodeText, nullable=False)
)

# Definition of the "bible_references" table
bible_references_table = Table('bible_references', metadata,
    Column('id', types.Integer, primary_key=True),
    Column('bible_id', types.Integer, ForeignKey('bibles.id'), nullable=False),
    Column('reference', types.Unicode(128), nullable=False),
    Column('text', types.UnicodeText, nullable=False)
)

# Definition of the "services" table
services_table = Table('services', metadata,
    Column('id', types.Integer, primary_key=True),
    Column('worship_group_id', types.Integer, ForeignKey('worship_groups.id'), nullable=False),
    Column('preacher_id', types.Integer, ForeignKey('preachers.id'), nullable=False),
    Column('sermon_title', types.Unicode(128)),
    Column('service_date', types.DateTime, default=datetime.now()),
    Column('created', types.DateTime, default=datetime.now()),
    Column('last_modified', types.DateTime, default=datetime.now()),
)

# Definition of the "preachers" table
preachers_table = Table('preachers', metadata,
    Column('id', types.Integer, primary_key=True),
    Column('name', types.Unicode(128), nullable=False)
)

# Definition of the "worship_groups" table
worship_groups_table = Table('worship_groups', metadata,
    Column('id', types.Integer, primary_key=True),
    Column('name', types.Unicode(128), nullable=False),
    Column('description', types.UnicodeText)
)

# Definition of the "capacities" table
capacities_table = Table('capacities', metadata,
    Column('id', types.Integer, primary_key=True),
    Column('name', types.Unicode(64), nullable=False)
)

# Definition of the "functions" table
capacities_table = Table('functions', metadata,
    Column('id', types.Integer, primary_key=True),
    Column('name', types.Unicode(64), nullable=False)
)

# Definition of the "worship_group_members" table
worship_group_members_table = Table('worship_group_members', metadata,
    Column('id', types.Integer, primary_key=True),
    Column('member_id', types.Integer, ForeignKey('members.id'), nullable=False),
    Column('worship_group_id', types.Integer, ForeignKey('worship_groups.id'), nullable=False),
    Column('capacity_id', types.Integer, ForeignKey('capacities.id'), nullable=False)
)

# Definition of the "service_members" table
service_members_table = Table('service_members', metadata,
    Column('id', types.Integer, primary_key=True),
    Column('member_id', types.Integer, ForeignKey('members.id'), nullable=False),
    Column('service_id', types.Integer, ForeignKey('services.id'), nullable=False),
    Column('function_id', types.Integer, ForeignKey('functions.id'), nullable=False)
)
